<?php
/*
 * ebiz front
 *
 * Customer Model
 *
 * @author li.jf
 *
 */

require_once '../app/core/models/BaseDao.php';
require_once '../app/core/db/DBUtil.class.php';

class QueryDao extends BaseDao
{
    private $tableName ="queries";

    public function saveQuery($param){
        $sql = "insert into $this->tableName
        (product_id, query_type, customer_id, status, content, modified, created)
        values (?,?,?,?,?,now(),now())";
        return $this->dbUtil->ExecuteQuery($sql, $param);
    }
    
 	public function getQueryInfoByItemId($itemId){
 		$param = array($itemId);
    	$sql="select   q.id as qid ,  product_id , query_type , customer_id , q.content as qcontent , q.created as qcreated ,
    	    qr.id as qrid , query_id , reply_id , qr.content as qrcontent ,
    	    c.name
    	    from   queries  q
			join  query_replies  qr   on    qr.query_id = q.id
			join users c  on q.customer_id = c.id
			where q.product_id = ?  and  qr.content  is not  null
			group by qr.query_id
			order by q.created DESC";
    	return $this->dbUtil->SelectData($sql,$param);
    }

    /**
     * Get user queries list
     *
     * @author by M.Wang
     * @param int $int_userid
     * @param int $int_page
     * @param int $int_limit
     * @return array
     */
    public function getQueryInfoByUserId( $int_userid, $int_page = 0, $int_limit = 10 ){
        $param = array($int_userid);
        $sql="
            SELECT SQL_CALC_FOUND_ROWS
                `q`.`id` AS `query_id`,
                `p`.`id` AS `product_id`,
                `p`.`title` AS `product_title`,
                `q`.`created` AS `query_created`,
                `q`.`content` AS `query_content`,
                `q`.`query_type` AS `query_type`,
                `q`.`status` AS `query_status` ,
                `r`.`created` AS `reply_created`,
                `r`.`content` AS `reply_content`,
                `a`.`username` AS `admin_name`
            FROM `{$this->tableName}` AS `q`
            LEFT JOIN `products` AS `p` ON `q`.`product_id` = `p`.`id`
            LEFT JOIN `query_replies` AS `r` ON `q`.`id` = `r`.`query_id`
            LEFT JOIN `admins` AS `a` ON `a`.`id` = `r`.`reply_id`
            WHERE `q`.`customer_id` = ? AND `q`.`is_delete` = 0
            LIMIT " . ( $int_page * $int_limit ) . ", {$int_limit}
        ";
        $arr_results = $this->dbUtil->SelectData($sql,$param);
        if( !empty( $arr_results ) ){
        	foreach ( $arr_results as $int_i => $arr_result ){
                $arr_results[$int_i]['query_type'] = $this->getQueryType( $arr_result['query_type'] );
                $arr_results[$int_i]['query_status'] = $this->getQueryStatus( $arr_result['query_status'] );
        	}
        }
        return $arr_results;
    }

    /**
     * Get user queries list
     *
     * @author by M.Wang
     * @param int $int_id products.id
     * @param int $int_limit limit set
     * @param int $int_rows page size
     * @param int $int_querytype queries.query_type
     * @param boolean $bool_onlyCount
     * @return array
     */
    public function getQueryInfoByProductId( $int_id, $int_limit = null, $int_rows = null, $int_querytype = null, $bool_onlyCount = false ){
        $param = array($int_id);
        if(!empty($int_querytype))
            $param[] = $int_querytype;
        if( $bool_onlyCount ){
            $sql = "SELECT COUNT(distinct `q`.`id`)  AS `ROWS` 
            FROM `{$this->tableName}` AS `q`
            LEFT JOIN `products` AS `p` ON `q`.`product_id` = `p`.`id`
            LEFT JOIN `query_replies` AS `r` ON `q`.`id` = `r`.`query_id`
            WHERE `p`.`id` = ? " . (empty($int_querytype)?'':' AND `q`.`query_type`=?') . " AND `q`.`is_delete` = 0 AND `r`.`content`  IS NOT NULL";
            //AND `r`.`content`  IS NOT NULL
        }else{
        $sql="
            SELECT SQL_CALC_FOUND_ROWS
                `q`.`id` AS `query_id`,
                `p`.`id` AS `product_id`,
                `p`.`title` AS `product_title`,
                `q`.`created` AS `query_created`,
                `q`.`content` AS `query_content`,
                `q`.`query_type` AS `query_type`,
                `q`.`status` AS `query_status` ,
                `r`.`created` AS `reply_created`,
                `r`.`modified` AS `reply_modified`,
                `r`.`content` AS `reply_content`,
                `a`.`username` AS `admin_name`,
                `u`.`name` AS `user_name`,
                max(`r`.`id`) AS reply_id
            FROM `{$this->tableName}` AS `q`
            LEFT JOIN `products` AS `p` ON `q`.`product_id` = `p`.`id`
            LEFT JOIN `query_replies` AS `r` ON `q`.`id` = `r`.`query_id`
            LEFT JOIN `admins` AS `a` ON `a`.`id` = `r`.`reply_id`
            LEFT JOIN `users` AS `u` ON `u`.`id` = `q`.`customer_id`
            WHERE `p`.`id` = ? " . (empty($int_querytype)?'':' AND `q`.`query_type`=?') . " AND `q`.`is_delete` = 0 AND `r`.`content`  IS NOT NULL
            GROUP BY `q`.`id` ORDER BY `q`.`created` DESC, `r`.`created` DESC
            LIMIT {$int_limit},{$int_rows}
        ";
            //AND `r`.`content`  IS NOT NULL
        }
        $arr_results = $this->dbUtil->SelectData($sql,$param);
        if( !empty( $arr_results ) && !$bool_onlyCount ){
        	foreach ( $arr_results as $int_i => $arr_result ){
        		
        		$replyId = $arr_result["reply_id"];
        		$queryReplyDao = new QueryReplyDao($this->dbUtil);
        		$queryReplyResult = $queryReplyDao->getReplyInfoById($replyId);
        		$arr_results[$int_i]['reply_content'] = $queryReplyResult[0]["content"];
        		$arr_results[$int_i]['reply_created'] = $queryReplyResult[0]["created"];
        		$arr_results[$int_i]['reply_modified'] = $queryReplyResult[0]["modified"];
        		
                $arr_results[$int_i]['query_type'] = $this->getQueryType( $arr_result['query_type'] );
                $arr_results[$int_i]['query_status'] = $this->getQueryStatus( $arr_result['query_status'] );
        	}
        }
        return $arr_results;
    }

    /**
     * Get user query by id
     *
     * @param int $int_queryid
     * @return array
     */
    public function getQueryInfoById( $int_queryid ){
    	$sql = "SELECT * FROM `{$this->tableName}` WHERE `id` = ? AND `is_delete` = 0";
    	return $this->dbUtil->SelectData( $sql, array( $int_queryid ) );
    }

    /**
     * Transform Query Type Setting
     *
     * @param int $int_typeid 1-5
     * @return string
     */
    public function getQueryType( $int_typeid ){
    	$arr_queryType = array();
        $arr_queryType[1] = QUERYTYPE_1;
        $arr_queryType[2] = QUERYTYPE_2;
        $arr_queryType[3] = QUERYTYPE_3;
        $arr_queryType[4] = QUERYTYPE_4;
        $arr_queryType[5] = QUERYTYPE_5;
        if( isset( $arr_queryType[$int_typeid] ) )
            return $arr_queryType[$int_typeid];
        return false;
    }

    public function getQueryStatus( $int_statusid ){
    	$arr_queryStatus = array();
        $arr_queryStatus[1] = QUERYSTATUS_1;
        $arr_queryStatus[2] = QUERYSTATUS_2;
        $arr_queryStatus[3] = QUERYSTATUS_3;
        if( isset( $arr_queryStatus[$int_statusid] ) )
            return $arr_queryStatus[$int_statusid];
        return false;
    }

}